create database studb;
use studb;
create table Class(
	ClassId int not null identity(1,1),
	ClassName nvarchar(50) not null unique
);
go

create table Student (
	StudentId int not null identity(1, 1),
	StudentName nvarchar(50),
	StudentSex tinyint not null default'3' check(studentsex='1' or studentsex='2' or studentsex='3'),
	StudentBirth date,
	StudentAddress nvarchar(255) not null,
	ClassId int not null
);
go
create table Course(
	CourseId int identity(1,1),
	CourseName nvarchar(50) unique,
	CourseCredit int check(CourseCredit>0)
);
go

create table ClassCourse(
	ClassCourseId int identity(1,1),
	ClassId int default'0',
	CourseId int
);
go

create table Score(
	ScoreId int identity(1,1),
	StudentId int,
	CourseId int,
	Score int check(Score>=0)
);
go
alter table student add StudentidentityCard varchar(20) not null default''
alter table score add IsResit tinyint not null default'0'

insert into Class (ClassName) values ('软件一班');
insert into Class (ClassName) values ('软件二班');
insert into Class (ClassName) values ('计算机应用技术班');

insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('刘正',1,'2000-01-01','广西省桂林市七星区空明西路10号鸾东小区', 1);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('黄贵',1,'2001-03-20','江西省南昌市青山湖区艾溪湖南路南150米广阳小区', 1);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('陈美',2,'2000-07-08','福建省龙岩市新罗区曹溪街道万达小区', 1);

insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('江文',1,'2000-08-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光', 2);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('钟琪',2,'2001-03-21','湖南省长沙市雨花区红花坡社区', 2);

insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('曾小林',1,'1999-12-10','安徽省合肥市庐阳区四里河路与潜山路交汇处万科城市之光', 3);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('欧阳天天',2,'2000-04-05','湖北省武汉市洪山区友谊大道与二环线交汇处融侨悦府', 3);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('徐长卿',1,'2001-01-30','江苏省苏州市苏州工业园区独墅湖社区', 3);
insert into Student (StudentName, StudentSex, StudentBirth, StudentAddress, ClassId) 
    values ('李逍遥',1,'1999-11-11','广东省广州市白云区金沙洲岛御洲三街恒大绿洲', 3);
insert into Student( StudentName, StudentSex, StudentBirth, StudentAddress)
values('东方不败', 2,'1999-12-11','河北省平定州西北四十余里的猩猩滩'),
		('令狐冲',1,'2000-08-11','陕西省渭南市华阴市玉泉路南段');

insert into Course(CourseName, CourseCredit) values ('数据库高级应用', 3);
insert into Course(CourseName, CourseCredit) values ('javascript编程基础', 3);
insert into Course(CourseName, CourseCredit) values ('web前端程序设计基础', 4);
insert into Course(CourseName, CourseCredit) values ('动态网页设计.net基础', 6);

insert into ClassCourse (ClassId, CourseId) values (1, 1);
insert into ClassCourse (ClassId, CourseId) values (1, 2);
insert into ClassCourse (ClassId, CourseId) values (1, 3);
insert into ClassCourse (ClassId, CourseId) values (1, 4);

insert into ClassCourse (ClassId, CourseId) values (2, 1);
insert into ClassCourse (ClassId, CourseId) values (2, 2);
insert into ClassCourse (ClassId, CourseId) values (2, 3);
insert into ClassCourse (ClassId, CourseId) values (2, 4);

insert into Course(CourseName, CourseCredit) values ('动态网页设计php基础', 6);

insert into ClassCourse (ClassId, CourseId) values (3, 1);
insert into ClassCourse (ClassId, CourseId) values (3, 2);
insert into ClassCourse (ClassId, CourseId) values (3, 3);
insert into ClassCourse (ClassId, CourseId) values (3, 5);

insert into Score (StudentId, CourseId, Score) values (1, 1, 80);
insert into Score (StudentId, CourseId, Score) values (1, 2, 78);
insert into Score (StudentId, CourseId, Score) values (1, 3, 65);
insert into Score (StudentId, CourseId, Score) values (1, 4, 90);

insert into Score (StudentId, CourseId, Score) values (2, 1, 60);
insert into Score (StudentId, CourseId, Score) values (2, 2, 77);
insert into Score (StudentId, CourseId, Score) values (2, 3, 68);
insert into Score (StudentId, CourseId, Score) values (2, 4, 88);

insert into Score (StudentId, CourseId, Score) values (3, 1, 88);
insert into Score (StudentId, CourseId, Score) values (3, 2, 45);
insert into Score (StudentId, CourseId, Score) values (3, 3, 66);
insert into Score (StudentId, CourseId, Score) values (3, 4, 75);
go
insert into Score (StudentId, CourseId, Score) values (4, 1, 56);
insert into Score (StudentId, CourseId, Score) values (4, 2, 80);
insert into Score (StudentId, CourseId, Score) values (4, 3, 75);
insert into Score (StudentId, CourseId, Score) values (4, 4, 66);

insert into Score (StudentId, CourseId, Score) values (5, 1, 88);
insert into Score (StudentId, CourseId, Score) values (5, 2, 79);
insert into Score (StudentId, CourseId, Score) values (5, 3, 72);
insert into Score (StudentId, CourseId, Score) values (5, 4, 85);

insert into Score (StudentId, CourseId, Score) values (6, 1, 68);
insert into Score (StudentId, CourseId, Score) values (6, 2, 88);
insert into Score (StudentId, CourseId, Score) values (6, 3, 73);
insert into Score (StudentId, CourseId, Score) values (6, 5, 63);

insert into Score (StudentId, CourseId, Score) values (7, 1, 84);
insert into Score (StudentId, CourseId, Score) values (7, 2, 90);
insert into Score (StudentId, CourseId, Score) values (7, 3, 92);
insert into Score (StudentId, CourseId, Score) values (7, 5, 78);

insert into Score (StudentId, CourseId, Score) values (8, 1, 58);
insert into Score (StudentId, CourseId, Score) values (8, 2, 59);
insert into Score (StudentId, CourseId, Score) values (8, 3, 65);
insert into Score (StudentId, CourseId, Score) values (8, 5, 75);

insert into Score (StudentId, CourseId, Score) values (9, 1, 48);
insert into Score (StudentId, CourseId, Score) values (9, 2, 67);
insert into Score (StudentId, CourseId, Score) values (9, 3, 71);
insert into Score (StudentId, CourseId, Score) values (9, 5, 56);
insert into Score (StudentId, CourseId, Score) values (9, 1, 48);

insert into Score (StudentId,CourseId,Score) values(10,1,null);
insert into Score (StudentId,CourseId,Score) values(10,2,null);
go

--日期函数练习
----------------------
--1.获取当前系统时间
select getdate()
--2.获取本月的第一天
select dateadd(month,datediff(month,0,getdate()),0)
--3.获取本月的最后一天
select dateadd(month,datediff(month,0,getdate())+1,0)-1
--3.获取下个月的第一天
select dateadd(month,datediff(month,0,getdate())+1,0)
--4.获取本周的 第一天
select dateadd(week,datediff(week,0,getdate()),-1)
--5.获取本周的最后一天
select dateadd(week,datediff(week,0,getdate())+1,-1)-1
--6.查询学生的出生年份信息
select distinct year(StudentBirth) from Student
--7.查询学生信息：姓名，年龄（年龄用datediff函数进行计算）
select *,datediff(year,StudentBirth,getdate()) 年龄 from Student
--8.查询本月生日的学生信息：学号，姓名，出生日期，年龄
select StudentId 学号,StudentName 姓名, StudentBirth 出生日期,
(year(getdate())-year(StudentBirth)) 年龄  from Student where month(StudentBirth)=month(getdate())
--9.查询下个月生日的学生信息
select * from Student where month(StudentBirth)=month(getdate())+1